package com.common.test;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

	/*
	@author 王嘉犇
	@name 从excel文件第二行读取数据，或是index，或是第一行列的值
	 */

public class GetDataFromImportFile {
	
	/**
	 * 根据index来取excel里的某列单行数据
	 */
	public static String getDataFromCell(String filename, int index){
		String data = null;
		try {
			InputStream is = GetDataFromImportFile.class.getResourceAsStream("/data/" + filename);
			HSSFWorkbook wbs = new HSSFWorkbook(is);
			Sheet sheet = wbs.getSheetAt(0);
			Row row1 = sheet.getRow(1);
			data = row1.getCell(index).toString();
			wbs.close();
			is.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return data;
	}
	
	/**
	 * 根据columnname来取excel里的某列单行数据
	 */
	public static String getOneLineDataFromCell(String filename, String columnname) throws Exception{
		String celldata = null;
		try {
			InputStream is = GetDataFromImportFile.class.getResourceAsStream("/data/" + filename);
			HSSFWorkbook wbs = new HSSFWorkbook(is);
			Sheet sheet = wbs.getSheetAt(0);
			int index = getIndexByColumnName(filename, columnname);
			Row row1 = sheet.getRow(1);
			celldata = row1.getCell(index).toString();
			wbs.close();
			is.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return celldata;
	}
	
	/**
	 * 根据columnname来取excel里的某列多行数据集合
	 */
	public static List<String> getMultiLineDataListFromIndex(String filename, String columnname) throws Exception{
		List<String> celldatalst = new ArrayList<String>();
		try {
			InputStream is = GetDataFromImportFile.class.getResourceAsStream("/data/" + filename);
			HSSFWorkbook wbs = new HSSFWorkbook(is);
			Sheet sheet = wbs.getSheetAt(0);
			int index = getIndexByColumnName(filename, columnname);
			int numberOfRows = sheet.getLastRowNum() + 1;
			for(int rowNum=1;rowNum<numberOfRows;rowNum++) {
				String celldata = null;
				Row row = sheet.getRow(rowNum);
				celldata = row.getCell(index).toString();
				celldatalst.add(celldata);
			}
			wbs.close();
			is.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return celldatalst;
	}
	
	/**
	 * 根据excel第一行的columnname得到是第几列
	 */
	private static int getIndexByColumnName(String filename, String columnname) throws Exception{
		int index = 0;
		boolean isfound = false;
		try {
			InputStream is = GetDataFromImportFile.class.getResourceAsStream("/data/" + filename);
			HSSFWorkbook wbs = new HSSFWorkbook(is);
			Sheet sheet = wbs.getSheetAt(0);
			Row row0 = sheet.getRow(0);
			int firstrowcount = firstEmptyCellPosition(row0);
			for(int i=0;i<firstrowcount;i++){
				if(row0.getCell(i).toString().equals(columnname)){
					isfound = true;
					index = i;
					break;
				}
			}
			wbs.close();
			is.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(!isfound){
			handlefailure("找不到相应的列名");
		}
		return index;
	}
	
	/**
	 * 取得excel第一行的有效列数
	 */
    private static int firstEmptyCellPosition(Row cells) {
        // TODO Auto-generated method stub
        int columnCount = 0;
        for (Cell cell : cells) {
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }
            columnCount++;
        }
        return columnCount;
    }
    
    /**
     * 抛出异常
     */
    private static void handlefailure(String msg) throws Exception{
    	throw new Exception(msg);
    }
}
